﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using DTO;

namespace DAL
{
    public class WebpageDAL
    {
        /// <summary>
        /// This method gets a webpage and then inserts this webpage (with only relative physical path and origianl URL) into database.
        /// </summary>
        /// <param name="path">The relative physical path of the webpage needs to be inserted into database.</param>
        /// <param name="OriginalUrl">The original URL of this webpage</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertWebpage(string path, string OriginalUrl)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertWebpage";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Path";
            param.Value = path;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // create a new parameter
            param = comm.CreateParameter();
            param.ParameterName = "@URL";
            if (OriginalUrl == null)
                param.Value = string.Empty;
            else
                param.Value = OriginalUrl;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method gets a keyword and then inserts this keyword into database.
        /// </summary>
        /// <param name="Keyword">The meta keyword needs to be inserted into database.</param>
        /// <param name="WebpageID">The webpage ID of the web page that this meta keyword belongs to</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertMetaKeyword(string Keyword, int WebpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertMetaKeyword";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Keyword";
            param.Value = Keyword;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method gets a link from source webpage to destination webpage and then inserts this link into database.
        /// </summary>
        /// <param name="sourceID">The source webpage ID in this link.</param>
        /// <param name="destID">The destination webpage ID in this link.</param>
        /// <param name="text">The text appears in this link.</param>
        /// <param name="sourceTagID">The ID of the source tag.</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertLink(int sourceID, int destID, string text, int sourceTagID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertLink";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@SourceID";
            param.Value = sourceID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@DestID";
            param.Value = destID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@Text";
            if (text == null)
                param.Value = string.Empty;
            else
                param.Value = text;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@SourceTagID";
            param.Value = sourceTagID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method retrieves the webpage ID from database with given URL
        /// </summary>
        /// <param name="Path">The relative physical path of the webpage needs to get ID</param>
        /// <returns>The ID of the webpage, or -1 if can not find.</returns>
        public static int GetWebpageID(string Path)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetWebpageID";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Path";
            param.Value = Path;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            if (dt.Rows.Count > 0)
            {
                return (int)dt.Rows[0]["ID"];
            }
            return -1;
        }

        /// <summary>
        /// This method retrieves the list of metakeywords ID of a webpage
        /// </summary>
        /// <param name="WebpageID">Current webpage ID needs to retrieved the metakeyword list.</param>
        /// <returns>The list of metakeyword ID belongs to this webpage.</returns>
        public static List<int> GetMetaKeywordList(int WebpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetMetaKeywordList";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            List<int> metaKeywordList = new List<int>();
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                string temp = (string)dr["MetaKeywords"];
                string[] tempList = temp.Split(',');
                foreach (string i in tempList)
                {
                    if (i.Equals("") == false)
                        metaKeywordList.Add(Convert.ToInt32(i));
                }
            }
            return metaKeywordList;
        }

        /// <summary>
        /// This method retrieves the list of titlekeywords ID of a webpage
        /// </summary>
        /// <param name="WebpageID">Current webpage ID needs to retrieved the titlekeyword list.</param>
        /// <returns>The list of titlekeyword ID belongs to this webpage.</returns>
        public static List<int> GetTitleKeywordList(int WebpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetTitleKeywordList";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            List<int> titleKeywordList = new List<int>();
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                string temp = (string)dr["TitleKeywords"];
                string[] tempList = temp.Split(',');
                foreach (string i in tempList)
                {
                    if (i.Equals("") == false)
                        titleKeywordList.Add(Convert.ToInt32(i));
                }
            }
            return titleKeywordList;
        }

        /// <summary>
        /// This method retrieves the list of Keywords of a webpage
        /// </summary>
        /// <param name="WebpageID">Current webpage ID needs to retrieved the Keywords list.</param>
        /// <returns>The list of Keywords belongs to this webpage.</returns>
        public static List<Keyword> GetKeywordsList(int WebpageID)
        {
            List<Keyword> keywordList = new List<Keyword>();
            List<int> metaIDList = GetMetaKeywordList(WebpageID);
            foreach (int curMetaID in metaIDList)
            {
                keywordList.Add(GetMetaKeyword(curMetaID));
            }

            return keywordList;
        }

        /// <summary>
        /// This method retrieves the list of Title Keywords of a webpage
        /// </summary>
        /// <param name="WebpageID">Current webpage ID needs to retrieved the Title Keywords list.</param>
        /// <returns>The list of Title Keywords belongs to this webpage.</returns>
        public static List<Keyword> GetTitleKeywordsList(int WebpageID)
        {
            List<Keyword> keywordList = new List<Keyword>();
            List<int> titleIDList = GetTitleKeywordList(WebpageID);
            foreach (int curTitleID in titleIDList)
            {
                keywordList.Add(GetTitleKeyword(curTitleID));
            }

            return keywordList;
        }

        /// <summary>
        /// This method retrieves the information of metakeyword.
        /// </summary>
        /// <param name="keywordID">The ID of the retrieved keyword.</param>
        /// <returns>Information of the retrieved keyword.</returns>
        public static Keyword GetMetaKeyword(int keywordID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetMetaKeyword";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@MetaID";
            param.Value = keywordID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            Keyword result = new Keyword();
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                result.Id = (int)dr["ID"];
                result.KeywordText = (string)dr["Keyword"];
                result.Count = (int)dr["Count"];

                return result;
            }
            return new Keyword();
        }

        /// <summary>
        /// This method retrieves the information of titlekeyword.
        /// </summary>
        /// <param name="keywordID">The ID of the retrieved keyword.</param>
        /// <returns>Information of the retrieved keyword.</returns>
        public static Keyword GetTitleKeyword(int keywordID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetTitleKeyword";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@TitleID";
            param.Value = keywordID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            Keyword result = new Keyword();
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                result.Id = (int)dr["ID"];
                result.KeywordText = (string)dr["Keyword"];
                result.Count = (int)dr["Count"];

                return result;
            }
            return new Keyword();
        }

        /// <summary>
        /// This method retrieves the list of all metakeywords
        /// </summary>
        /// <returns>The list of all metakeywords.</returns>
        public static List<Keyword> GetAllMetaKeywords()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetAllMetaKeywords";

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            List<Keyword> metaKeywordList = new List<Keyword>();
            foreach (DataRow dr in dt.Rows)
            {
                Keyword curKeyword = new Keyword();

                curKeyword.Id = (int)dr["ID"];
                curKeyword.KeywordText = (string)dr["Keyword"];
                curKeyword.Count = (int)dr["Count"];
                metaKeywordList.Add(curKeyword);
            }
            return metaKeywordList;
        }

        /// <summary>
        /// This method retrieves the list of all metakeywords
        /// </summary>
        /// <returns>The list of all metakeywords.</returns>
        public static List<Keyword> GetAllTitleKeywords()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetAllTitleKeywords";

            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            List<Keyword> titleKeywordList = new List<Keyword>();
            foreach (DataRow dr in dt.Rows)
            {
                Keyword curKeyword = new Keyword();

                curKeyword.Id = (int)dr["ID"];
                curKeyword.KeywordText = (string)dr["Keyword"];
                curKeyword.Count = (int)dr["Count"];
                titleKeywordList.Add(curKeyword);
            }
            return titleKeywordList;
        }

        /// <summary>
        /// This method updates the information of a webpage.
        /// </summary>
        /// <param name="Title">The title of this webpage.</param>
        /// <param name="PlainText">The plaintext (tag stripted) of this webpage.</param>
        /// <param name="WebpageID">Current ID of this webpage.</param>
        /// <returns>True if update successfully, false if not</returns>
        public static bool UpdateWebpage(string Title, int WebpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "UpdateWebpage";
            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Title";
            param.Value = Title;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method deletes an empty webpage.
        /// </summary>
        /// <param name="WebpageID">Current ID of this webpage.</param>
        /// <returns>True if delete successfully, false if not</returns>
        public static bool DeleteEmptyWebpage(int WebpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "DeleteEmptyWebpage";
            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method retrieves all webpages in database.
        /// </summary>
        /// <returns>List of all webpages.</returns>
        public static List<Webpage> GetWebpages()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetWebpages";

            List<Webpage> pagesList = new List<Webpage>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Webpage curPage = new Webpage();
                curPage.ID = (int)dr["ID"];
                curPage.Path = (string)dr["Path"];
                curPage.URL = (string)dr["URL"];
                if (dr["Title"] == System.DBNull.Value)
                    curPage.Title = string.Empty;
                else
                    curPage.Title = (string)dr["Title"];
                pagesList.Add(curPage);
            }
            return pagesList;
        }

        /// <summary>
        /// This method retrieves all webpages in database.
        /// </summary>
        /// <returns>List of all webpages.</returns>
        public static List<Webpage> GetWebpagesNoChecking()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetWebpagesNoChecking";

            List<Webpage> pagesList = new List<Webpage>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Webpage curPage = new Webpage();
                curPage.ID = (int)dr["ID"];
                curPage.Path = (string)dr["Path"];
                curPage.URL = (string)dr["URL"];
                if (dr["Title"] == System.DBNull.Value)
                    curPage.Title = string.Empty;
                else
                    curPage.Title = (string)dr["Title"];
                pagesList.Add(curPage);
            }
            return pagesList;
        }

        /// <summary>
        /// This method will remove a link which is in duplicated region with given webtagID
        /// </summary>
        /// <param name="WebtagID">The ID of the current webtagID that contains this link.</param>
        /// <returns>True if delete successfully, false if not</returns>
        public static bool DeleteLink(int WebtagID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "DeleteLink";
            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebtagID";
            param.Value = WebtagID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }        

        /// <summary>
        /// This method updates the original URL of the current webpage.
        /// </summary>
        /// <param name="WebpageID">Current webpage ID.</param>
        /// <param name="originalURL">The original URL of this webpage</param>
        /// <returns>True if update successfully, false if not</returns>
        public static bool UpdateWebpageURL(int WebpageID, string originalURL)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "UpdateWebpageURL";
            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@URL";
            param.Value = originalURL;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method retrieves all URL of this current domain from database
        /// </summary>
        /// <returns>List of all webpage with only ID and original URL</returns>
        public static List<Webpage> GetOriginalURLs()
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetOriginalURLs";

            List<Webpage> pagesList = new List<Webpage>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Webpage curPage = new Webpage();
                curPage.ID = (int)dr["ID"];
                if (dr["URL"] != System.DBNull.Value)
                    curPage.URL = (string)dr["URL"];
                pagesList.Add(curPage);
            }
            return pagesList;
        }

        /// <summary>
        /// Retrieve all links from webpage with webpageID.
        /// </summary>
        /// <param name="webpageID">The ID of the source webpage</param>
        /// <returns>List of all links which have webpageID as source webpage ID</returns>
        public static List<Link> GetLinks(int webpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetLinks";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = webpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            List<Link> linkList = new List<Link>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Link curLink = new Link();
                curLink.SourceWebpageID = (int)dr["SourceID"];
                curLink.DestWebpageID = (int)dr["DestID"];
                linkList.Add(curLink);
            }
            return linkList;
        }

        public static List<Webtag> GetAnchorTags(int webpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetAnchorTags";

            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = webpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            List<Webtag> anchorTagList = new List<Webtag>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Webtag curWebtag = new Webtag();
                curWebtag.Text = (string)dr["Text"];
                curWebtag.ID = (int)dr["ID"];
                anchorTagList.Add(curWebtag);
            }
            return anchorTagList;
        }

        public static bool UpdateLink(int linkID, string linkText, int tagID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "UpdateLink";
            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@LinkID";
            param.Value = linkID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@LinkText";
            param.Value = linkText;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);
          
            param = comm.CreateParameter();
            param.ParameterName = "@TagID";
            param.Value = tagID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }

        /// <summary>
        /// This method retrieves all webpages in database.
        /// </summary>
        /// <param name="beginID"></param>
        /// <param name="endID"></param>
        /// <returns>List of all webpages.</returns>
        public static List<Webpage> GetWebpagesRange(int beginID, int endID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "GetWebpagesRange";
            
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@begin";
            param.Value = beginID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@end";
            param.Value = endID;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            List<Webpage> pagesList = new List<Webpage>();
            // execute the stored procedure and return the results
            DataTable dt = GenericDataAccess.ExecuteSelectCommand(comm);
            foreach (DataRow dr in dt.Rows)
            {
                Webpage curPage = new Webpage();
                curPage.ID = (int)dr["ID"];
                curPage.Path = (string)dr["Path"];
                pagesList.Add(curPage);
            }
            return pagesList;
        }

        /// <summary>
        /// This method gets a keyword and then inserts this keyword into database.
        /// </summary>
        /// <param name="Keyword">The title keyword needs to be inserted into database.</param>
        /// <param name="WebpageID">The webpage ID of the web page that this title keyword belongs to</param>
        /// <returns>True if insert successfully, false if not</returns>
        public static bool InsertTitleKeyword(string Keyword, int WebpageID)
        {
            // get a configured DbCommand object
            DbCommand comm = GenericDataAccess.CreateCommand();
            // set the stored procedure name
            comm.CommandText = "InsertTitleKeyword";

            // create a new parameter
            DbParameter param = comm.CreateParameter();
            param.ParameterName = "@Keyword";
            param.Value = Keyword;
            param.DbType = DbType.String;
            comm.Parameters.Add(param);

            param = comm.CreateParameter();
            param.ParameterName = "@WebpageID";
            param.Value = WebpageID;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute the stored procedure and save the results in a DataTable
            return GenericDataAccess.ExecuteNonQuery(comm) > 0;
        }
    }
}
